CREATE SCHEMA dataset;
CREATE SCHEMA ml_model;
CREATE SCHEMA pipeline;

CREATE EXTENSION postgis;
CREATE EXTENSION plpython3u;

CREATE TABLE dataset."_city_mapper_" (
	zh_full text NULL,
	zh text NULL,
	en text NULL,
	zipcode text NULL,
	province text NULL,
	lon numeric NULL,
	lat numeric NULL,
	id int4 NULL,
	geom text NULL
);

CREATE TABLE dataset."_country_mapper_" (
	zh_full text NULL,
	zh text NULL,
	en_full text NULL,
	en text NULL,
	iso2 text NULL,
	iso3 text NULL,
	phone text NULL,
	lon numeric NULL,
	lat numeric NULL
);

CREATE TABLE dataset."_province_mapper_" (
	zh text NULL,
	zh_full text NULL,
	zh_abbr text NULL,
	en text NULL,
	en_full text NULL,
	en_abbr text NULL,
	zipcode text NULL,
	country text NULL,
	lon numeric NULL,
	lat numeric NULL,
	geom text NULL
);

\copy dataset._city_mapper_ FROM '/tmp/_city_mapper_.csv' WITH csv header delimiter ',' encoding 'UTF8';
\copy dataset._country_mapper_ FROM '/tmp/_country_mapper_.csv' WITH csv header delimiter ',' encoding 'UTF8';
\copy dataset._province_mapper_ FROM '/tmp/_province_mapper_.csv' WITH csv header delimiter ',' encoding 'UTF8';

CREATE OR REPLACE FUNCTION "public"."geoc_bd09towgs84_multipolygon"("source_geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
target_parts    geometry[];
    single_polygon  geometry;
    single_polygon_trans  geometry;
    final_geom      geometry;

BEGIN
    IF ST_GeometryType(source_geom) != 'ST_MultiPolygon' THEN
        RETURN null;
END IF;
FOR single_polygon IN SELECT (ST_Dump($1)).geom LOOP
        single_polygon_trans := geoc_bd09towgs84_polygon(single_polygon);
target_parts := array_append(target_parts,single_polygon_trans);
END LOOP;

SELECT st_multi(ST_Union(target_parts)) INTO final_geom;
RETURN final_geom;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_bd09towgs84_point"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
x       numeric;
    y       numeric;
    gcj_point  geometry;
    wgs_point  geometry;

BEGIN
    if st_geometrytype(geom) != 'ST_Point' then
      return null;
end if;
    x := st_x(geom);
    y := st_y(geom);
    if (geoc_is_in_china_bbox(x, y) = false) then
      return geom;
end if;
    gcj_point = geoc_bd09togcj02_point(geom);
      wgs_point = geoc_gcj02towgs84_point(gcj_point);
return wgs_point;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_bd09towgs84_polygon"("source_geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
target_parts    geometry[];
    source_npoints  integer;
    single_line     geometry;
    single_line_trans geometry;
    single_polygon  geometry;
    final_geom      geometry;

BEGIN
    IF ST_GeometryType(source_geom) != 'ST_Polygon' THEN
        RETURN null;
END IF;

FOR single_polygon IN SELECT ST_ExteriorRing ((st_dumprings($1)).geom) as geom LOOP
        source_npoints := ST_NPoints(single_polygon);
single_line  := ST_RemovePoint(single_polygon, source_npoints - 1);
        single_line_trans := geoc_bd09towgs84_line(single_line);
        target_parts := array_append(target_parts, ST_AddPoint(single_line_trans, ST_PointN(single_line_trans, 1)));
END LOOP;
SELECT ST_MakePolygon(target_parts[1], target_parts[2:array_upper(target_parts, 1)]) INTO final_geom;
RETURN final_geom;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_cgcs2000tobd09"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
BEGIN
IF st_srid(geom) != '4490' THEN
        RETURN null;
end if;
return geoc_wgs84tobd09(st_transform(st_setsrid(geom,4490),4326));
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_cgcs2000togcj02"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
BEGIN
IF st_srid(geom) != '4490' THEN
        RETURN null;
end if;
return geoc_wgs84togcj02(st_transform(st_setsrid(geom,4490),4326));
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_delta"("lon" numeric, "lat" numeric)
  RETURNS "pg_catalog"."jsonb" AS $BODY$
DECLARE
ret             varchar;
    dLon            numeric;
    dlat            numeric;
    radLat          numeric;
    magic           numeric;
    sqrtMagic       numeric;
    ee              numeric;
    a               numeric;
BEGIN
    ee := 0.006693421622965823;
    a  := 6378245;
    dLon := geoc_transform_lon(lon - 105, lat - 35);
    dLat := geoc_transform_lat(lon - 105, lat - 35);
    radLat := lat / 180 * pi();
    magic = sin(radLat);

    magic = 1 - ee * magic * magic;

    sqrtMagic := sqrt(magic);
    dLon = (dLon * 180) / (a / sqrtMagic * cos(radLat) * pi());
    dLat = (dLat * 180) / ((a * (1 - ee)) / (magic * sqrtMagic) * pi());
    ret :='['||dLon||','||dLat||']';
return ret::jsonb;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_gcj02tobd09"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
BEGIN
IF st_srid(geom) != '4490' and  st_srid(geom) != '4326'THEN
        RETURN null;
end if;
case ST_GeometryType(geom)
    when 'ST_LineString' then
            return geoc_gcj02tobd09_line(geom);
when 'ST_MultiLineString' then
        return geoc_gcj02tobd09_multiline(geom);
when 'ST_Point' then
        return geoc_gcj02tobd09_point(geom);
when 'ST_MultiPoint' then
            return geoc_gcj02tobd09_multipoint(geom);
when 'ST_Polygon' then
            return geoc_gcj02tobd09_polygon(geom);
when 'ST_MultiPolygon' then
        return geoc_gcj02tobd09_multipolygon(geom);
ELSE
        RETURN null;
END CASE;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_gcj02tobd09_line"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
p_p     geometry;
        p_t     geometry;
        z_t     geometry;
        i       int;
BEGIN
    i:=1;
    while i <= st_npoints(geom) LOOP
        p_p := st_pointn(geom,i);
        p_t := geoc_gcj02tobd09_point(p_p);
        geom:=st_setpoint(geom,i-1,p_t);
        i:=i+1;
end LOOP;
return geom;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_gcj02tobd09_multiline"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
i                 geometry;
        transform_i       geometry;
        multiArr          geometry[];

BEGIN
    multiArr:='{}'::geometry[];
for i in EXECUTE $Q$ select (st_dump($1)).geom  $Q$ using geom LOOP
          transform_i :=geoc_gcj02tobd09_line(i);
multiArr := array_append(multiArr, transform_i);
end LOOP;
return st_multi(ST_Union(multiArr));
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_gcj02tobd09_multipoint"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
i                 geometry;
        transform_i       geometry;
        multiArr          geometry[];

BEGIN
    multiArr:='{}'::geometry[];
for i in EXECUTE $Q$ select (st_dump($1)).geom  $Q$ using geom LOOP
          transform_i :=geoc_gcj02tobd09_point(i);
multiArr := array_append(multiArr, transform_i);
end LOOP;
return st_multi(ST_Union(multiArr));
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_gcj02tobd09_multipolygon"("source_geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
target_parts    geometry[];
    single_polygon  geometry;
    single_polygon_trans  geometry;
    final_geom      geometry;

BEGIN
    IF ST_GeometryType(source_geom) != 'ST_MultiPolygon' THEN
        RETURN null;
END IF;
FOR single_polygon IN SELECT (ST_Dump($1)).geom LOOP
                single_polygon_trans := geoc_gcj02tobd09_polygon(single_polygon);
target_parts := array_append(target_parts,single_polygon_trans);
END LOOP;

SELECT st_multi(ST_Union(target_parts)) INTO final_geom;
RETURN final_geom;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_gcj02tobd09_point"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
z         double precision;
theta     double precision;
x_pi      double precision:=3.14159265358979324 * 3000.0 / 180.0;
lon       numeric;
lat       numeric;
bd_point  geometry;

BEGIN
    if st_geometrytype(geom) != 'ST_Point' then
      return null;
end if;
    lon := st_x(geom);
    lat := st_y(geom);
    if geoc_is_in_china_bbox(lon, lat) = false THEN
      return geom;
end if;
    z:= sqrt(power(lon,2) + power(lat,2)) + 0.00002 * sin(lat * x_pi);
    theta:= atan2(lat, lon) + 0.000003 * cos(lon * x_pi);
    bd_point:=ST_SetSRID(ST_MakePoint(z * cos(theta) + 0.0065,z * sin(theta) + 0.006),4326);
return bd_point;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_gcj02tobd09_polygon"("source_geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
target_parts    geometry[];
    source_npoints  integer;
    single_line     geometry;
    single_line_trans geometry;
    single_polygon  geometry;
    final_geom      geometry;

BEGIN
    IF ST_GeometryType(source_geom) != 'ST_Polygon' THEN
        RETURN null;
END IF;

FOR single_polygon IN SELECT ST_ExteriorRing ((st_dumprings($1)).geom) as geom LOOP
        source_npoints := ST_NPoints(single_polygon);
single_line  := ST_RemovePoint(single_polygon, source_npoints - 1);
        single_line_trans := geoc_gcj02tobd09_line(single_line);
        target_parts := array_append(target_parts, ST_AddPoint(single_line_trans, ST_PointN(single_line_trans, 1)));
END LOOP;
SELECT ST_MakePolygon(target_parts[1], target_parts[2:array_upper(target_parts, 1)]) INTO final_geom;
RETURN final_geom;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_gcj02tocgcs2000"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
BEGIN
IF st_srid(geom) != '4490' THEN
        RETURN null;
end if;
return st_transform(st_setsrid(geoc_gcj02towgs84(geom),4326),4490);
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_gcj02towgs84"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
BEGIN
IF st_srid(geom) != '4490' and  st_srid(geom) != '4326'THEN
        RETURN null;
end if;
case ST_GeometryType(geom)
    when 'ST_LineString' then
            return geoc_gcj02towgs84_line(geom);
when 'ST_MultiLineString' then
        return geoc_gcj02towgs84_multiline(geom);
when 'ST_Point' then
        return geoc_gcj02towgs84_point(geom);
when 'ST_MultiPoint' then
            return geoc_gcj02towgs84_multipoint(geom);
when 'ST_Polygon' then
            return geoc_gcj02towgs84_polygon(geom);
when 'ST_MultiPolygon' then
        return geoc_gcj02towgs84_multipolygon(geom);
ELSE
        RETURN null;
END CASE;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_gcj02towgs84_line"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
p_p     geometry;
        p_t     geometry;
        z_t     geometry;
        i       int;
BEGIN
    i:=1;
    while i <= st_npoints(geom) LOOP
        p_p := st_pointn(geom,i);
        p_t := geoc_gcj02towgs84_point(p_p);
        geom:=st_setpoint(geom,i-1,p_t);
        i:=i+1;
end LOOP;
return geom;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_gcj02towgs84_multiline"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
i                 geometry;
        transform_i       geometry;
        multiArr          geometry[];

BEGIN
    multiArr:='{}'::geometry[];
for i in EXECUTE $Q$ select (st_dump($1)).geom  $Q$ using geom LOOP
          transform_i :=geoc_gcj02towgs84_line(i);
multiArr := array_append(multiArr, transform_i);
end LOOP;
return st_multi(ST_Union(multiArr));
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_gcj02towgs84_multipoint"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
i                 geometry;
        transform_i       geometry;
        multiArr          geometry[];

BEGIN
    multiArr:='{}'::geometry[];
for i in EXECUTE $Q$ select (st_dump($1)).geom  $Q$ using geom LOOP
          transform_i :=geoc_gcj02towgs84_point(i);
multiArr := array_append(multiArr, transform_i);
end LOOP;
return st_multi(ST_Union(multiArr));
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_gcj02towgs84_multipolygon"("source_geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
target_parts    geometry[];
    single_polygon  geometry;
    single_polygon_trans  geometry;
    final_geom      geometry;

BEGIN
    IF ST_GeometryType(source_geom) != 'ST_MultiPolygon' THEN
        RETURN null;
END IF;
FOR single_polygon IN SELECT (ST_Dump($1)).geom LOOP
                single_polygon_trans := geoc_gcj02towgs84_polygon(single_polygon);
target_parts := array_append(target_parts,single_polygon_trans);
END LOOP;

SELECT st_multi(ST_Union(target_parts)) INTO final_geom;
RETURN final_geom;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_gcj02towgs84_point"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
tempPoint  numeric[];
    wgsLon     numeric;
    wgsLat     numeric;
    lon        numeric;
    lat        numeric;
BEGIN
    if st_geometrytype(geom) != 'ST_Point' then
      return null;
end if;
    lon := st_x(geom);
    lat := st_y(geom);
    if geoc_is_in_china_bbox(lon, lat) = false THEN
      return geom;
end if;

    tempPoint := geoc_wgs84togcj02(ARRAY[lon, lat]);
    wgsLon := lon*2 - tempPoint[1];
    wgsLat := lat*2 - tempPoint[2];
return st_makepoint(wgsLon,wgsLat);
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_gcj02towgs84_polygon"("source_geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
target_parts    geometry[];
    source_npoints  integer;
    single_line     geometry;
    single_line_trans geometry;
    single_polygon  geometry;
    final_geom      geometry;

BEGIN
    IF ST_GeometryType(source_geom) != 'ST_Polygon' THEN
        RETURN null;
END IF;

FOR single_polygon IN SELECT ST_ExteriorRing ((st_dumprings($1)).geom) as geom LOOP
        source_npoints := ST_NPoints(single_polygon);
single_line  := ST_RemovePoint(single_polygon, source_npoints - 1);
        single_line_trans := geoc_gcj02towgs84_line(single_line);
        target_parts := array_append(target_parts, ST_AddPoint(single_line_trans, ST_PointN(single_line_trans, 1)));
END LOOP;
SELECT ST_MakePolygon(target_parts[1], target_parts[2:array_upper(target_parts, 1)]) INTO final_geom;
RETURN final_geom;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_is_in_china_bbox"("lon" numeric, "lat" numeric)
  RETURNS "pg_catalog"."bool" AS $BODY$
DECLARE
BEGIN

return lon >= 72.004 and lon <= 137.8347 and lat >= 0.8293 and lat <= 55.8271;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_transform_lat"("x" numeric, "y" numeric)
  RETURNS "pg_catalog"."numeric" AS $BODY$
DECLARE
ret   numeric;
BEGIN
    ret := -100 + 2 * x + 3 * y + 0.2 * y * y + 0.1 * x * y + 0.2 * sqrt(abs(x));
    ret := ret + (20 * sin(6 * x * PI()) + 20 * sin(2 * x * PI())) * 2 / 3;
    ret := ret +(20 * sin(y * PI()) + 40 * sin(y / 3 * PI())) * 2 / 3;
    ret := ret +(160 * sin(y / 12 * PI()) + 320 * sin(y * PI() / 30)) * 2 / 3;
return ret;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_transform_lon"("x" numeric, "y" numeric)
  RETURNS "pg_catalog"."numeric" AS $BODY$
DECLARE
ret   numeric;
BEGIN
    ret := 300 + x + 2 * y + 0.1 * x * x + 0.1 * x * y + 0.1 * sqrt(abs(x));
    ret :=ret + (20 * sin(6 * x * pi()) + 20 * sin(2 * x * pi())) * 2 / 3;
    ret :=ret + (20 * sin(x * pi()) + 40 * sin(x / 3 * pi())) * 2 / 3;
    ret :=ret + (150 * sin(x / 12 * pi()) + 300 * sin(x / 30 * pi())) * 2 / 3;
return ret;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_wgs84tobd09"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
BEGIN
IF st_srid(geom) != '4490' and  st_srid(geom) != '4326'THEN
        RETURN null;
end if;
case ST_GeometryType(geom)
  when 'ST_LineString' then
        return geoc_wgs84tobd09_line(geom);
when 'ST_MultiLineString' then
        return geoc_wgs84tobd09_multiline(geom);
when 'ST_Point' then
        return geoc_wgs84tobd09_point(geom);
when 'ST_MultiPoint' then
        return geoc_wgs84tobd09_multipoint(geom);
when 'ST_Polygon' then
        return geoc_wgs84tobd09_polygon(geom);
when 'ST_MultiPolygon' then
        return geoc_wgs84tobd09_multipolygon(geom);
ELSE
        RETURN null;
END CASE;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_wgs84tobd09_line"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
p_p     geometry;
        p_t     geometry;
        z_t     geometry;
        i       int;
BEGIN
    i:=1;
    while i <= st_npoints(geom) LOOP
        p_p := st_pointn(geom,i);
        p_t := geoc_wgs84tobd09_point(p_p);
        geom:=st_setpoint(geom,i-1,p_t);
        i:=i+1;
end LOOP;
return geom;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_wgs84tobd09_multiline"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
i                 geometry;
        transform_i       geometry;
        multiArr          geometry[];

BEGIN
    multiArr:='{}'::geometry[];
for i in EXECUTE $Q$ select (st_dump($1)).geom  $Q$ using geom LOOP
          transform_i :=geoc_wgs84tobd09_line(i);
multiArr := array_append(multiArr, transform_i);
end LOOP;
return st_multi(ST_Union(multiArr));
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_wgs84tobd09_multipoint"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
i                 geometry;
        transform_i       geometry;
        multiArr          geometry[];

BEGIN
    multiArr:='{}'::geometry[];
for i in EXECUTE $Q$ select (st_dump($1)).geom  $Q$ using geom LOOP
          transform_i :=geoc_wgs84tobd09_point(i);
multiArr := array_append(multiArr, transform_i);
end LOOP;
return st_multi(ST_Union(multiArr));
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_wgs84tobd09_multipolygon"("source_geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
target_parts    geometry[];
    single_polygon  geometry;
    single_polygon_trans  geometry;
    final_geom      geometry;

BEGIN
    IF ST_GeometryType(source_geom) != 'ST_MultiPolygon' THEN
        RETURN null;
END IF;
FOR single_polygon IN SELECT (ST_Dump($1)).geom LOOP
                single_polygon_trans := geoc_wgs84tobd09_polygon(single_polygon);
target_parts := array_append(target_parts,single_polygon_trans);
END LOOP;

SELECT st_multi(ST_Union(target_parts)) INTO final_geom;
RETURN final_geom;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_wgs84tobd09_point"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
lon       numeric;
lat       numeric;
bd_point  geometry;
gcj_point  geometry;

BEGIN
    if st_geometrytype(geom) != 'ST_Point' then
      return null;
end if;
    lon := st_x(geom);
    lat := st_y(geom);
    if geoc_is_in_china_bbox(lon, lat) = false THEN
      return geom;
end if;
    gcj_point = geoc_wgs84togcj02_point(geom);
        bd_point = geoc_gcj02tobd09_point(gcj_point);
return bd_point;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_wgs84tobd09_polygon"("source_geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
target_parts    geometry[];
    source_npoints  integer;
    single_line     geometry;
    single_line_trans geometry;
    single_polygon  geometry;
    final_geom      geometry;

BEGIN
    IF ST_GeometryType(source_geom) != 'ST_Polygon' THEN
        RETURN null;
END IF;

FOR single_polygon IN SELECT ST_ExteriorRing ((st_dumprings($1)).geom) as geom LOOP
        source_npoints := ST_NPoints(single_polygon);
single_line  := ST_RemovePoint(single_polygon, source_npoints - 1);
        single_line_trans := geoc_wgs84tobd09_line(single_line);
        target_parts := array_append(target_parts, ST_AddPoint(single_line_trans, ST_PointN(single_line_trans, 1)));
END LOOP;
SELECT ST_MakePolygon(target_parts[1], target_parts[2:array_upper(target_parts, 1)]) INTO final_geom;
RETURN final_geom;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_wgs84togcj02"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
i                 geometry;
        transform_i       geometry;
        multiArr          geometry[];

BEGIN
IF st_srid(geom) != '4490' and  st_srid(geom) != '4326'THEN
        RETURN null;
end if;
CASE ST_GeometryType(geom)
        when 'ST_LineString' then
            return geoc_wgs84togcj02_line(geom);
when 'ST_MultiLineString' then
            return geoc_wgs84togcj02_multiline(geom);
when 'ST_Point' then
            return geoc_wgs84togcj02_point(geom);
when 'ST_MultiPoint' then
            return geoc_wgs84togcj02_multipoint(geom);
when 'ST_Polygon' then
            return geoc_wgs84togcj02_polygon(geom);
when 'ST_MultiPolygon' then
            return geoc_wgs84togcj02_multipolygon(geom);
ELSE
             RETURN null;
END CASE;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_wgs84togcj02_line"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
p_p     geometry;
        p_t     geometry;
        z_t     geometry;
        i       int;
BEGIN
    i:=1;
    while i <= st_npoints(geom) LOOP
        p_p := st_pointn(geom,i);
        p_t := geoc_wgs84togcj02_point(p_p);
        geom:=st_setpoint(geom,i-1,p_t);
        i:=i+1;
end LOOP;
return geom;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_wgs84togcj02_multiline"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
i                 geometry;
        transform_i       geometry;
        multiArr          geometry[];

BEGIN
    multiArr:='{}'::geometry[];
for i in EXECUTE $Q$ select (st_dump($1)).geom $Q$ using geom LOOP
          transform_i :=geoc_wgs84togcj02_line(i);
multiArr := array_append(multiArr, transform_i);
end LOOP;
return st_multi(ST_Union(multiArr));
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_wgs84togcj02_multipoint"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
i                 geometry;
        transform_i       geometry;
        multiArr          geometry[];

BEGIN
    multiArr:='{}'::geometry[];
for i in EXECUTE $Q$ select (st_dump($1)).geom $Q$ using geom LOOP
          transform_i :=geoc_wgs84togcj02_point(i);
multiArr := array_append(multiArr, transform_i);
end LOOP;
return st_multi(ST_Union(multiArr));
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_wgs84togcj02_multipolygon"("source_geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
target_parts    geometry[];
    single_polygon  geometry;
    single_polygon_trans  geometry;
    final_geom      geometry;

BEGIN
    IF ST_GeometryType(source_geom) != 'ST_MultiPolygon' THEN
        RETURN null;
END IF;
FOR single_polygon IN SELECT (ST_Dump($1)).geom LOOP
        single_polygon_trans := geoc_wgs84togcj02_polygon(single_polygon);
target_parts := array_append(target_parts,single_polygon_trans);
END LOOP;

SELECT st_multi(ST_Union(target_parts)) INTO final_geom;
RETURN final_geom;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_wgs84togcj02"("coord" _numeric)
  RETURNS "pg_catalog"."_numeric" AS $BODY$
DECLARE
ret             numeric[];
    dLon            numeric;
    dlat            numeric;
        lon             numeric;
        lat             numeric;
        d                                jsonb;
--         coord           ARRAY;
BEGIN
    lon := coord[1];
    lat := coord[2];
    if (geoc_is_in_china_bbox(lon, lat) = false) then
        return coord;
end if;
    d := geoc_delta(lon, lat);
    dlon := d->0;
    dlat := d->1;
    ret := ARRAY[lon + dlon , lat + dlat];
return ret;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_wgs84togcj02_point"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
lon     numeric;
    lat     numeric;
    d       jsonb;
    dlon    numeric;
    dlat    numeric;
BEGIN
    if st_geometrytype(geom) != 'ST_Point' then
        return null;
end if;
    lon := st_x(geom);
    lat := st_y(geom);
    if (geoc_is_in_china_bbox(lon, lat) = false) then
        return geom;
end if;
    d := geoc_delta(lon, lat);
    dlon := d->0;
    dlat := d->1;
return st_makepoint(lon + dlon,lat + dlat);
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_wgs84togcj02_polygon"("source_geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
target_parts    geometry[];
    source_npoints  integer;
    single_line     geometry;
    single_line_trans geometry;
    single_polygon  geometry;
    final_geom      geometry;

BEGIN
    IF ST_GeometryType(source_geom) != 'ST_Polygon' THEN
        RETURN null;
END IF;

FOR single_polygon IN SELECT ST_ExteriorRing ((st_dumprings($1)).geom) as geom LOOP
        source_npoints := ST_NPoints(single_polygon);
single_line  := ST_RemovePoint(single_polygon, source_npoints - 1);
        single_line_trans := geoc_wgs84togcj02_line(single_line);
        target_parts := array_append(target_parts, ST_AddPoint(single_line_trans, ST_PointN(single_line_trans, 1)));
END LOOP;
SELECT ST_MakePolygon(target_parts[1], target_parts[2:array_upper(target_parts, 1)]) INTO final_geom;
RETURN final_geom;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_bd09tocgcs2000"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
BEGIN
IF st_srid(geom) != '4490' THEN
        RETURN null;
end if;
return st_transform(st_setsrid(geoc_bd09towgs84(geom),4326),4490);
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_bd09togcj02"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
i                 geometry;
        transform_i       geometry;
        multiArr          geometry[];

BEGIN
IF st_srid(geom) != '4490' and  st_srid(geom) != '4326'THEN
        RETURN null;
end if;
CASE ST_GeometryType(geom)
        when 'ST_LineString' then
            return geoc_bd09togcj02_line(geom);
when 'ST_MultiLineString' then
            return geoc_bd09togcj02_multiline(geom);
when 'ST_Point' then
            return geoc_bd09togcj02_point(geom);
when 'ST_MultiPoint' then
            return geoc_bd09togcj02_multipoint(geom);
when 'ST_Polygon' then
            return geoc_bd09togcj02_polygon(geom);
when 'ST_MultiPolygon' then
            return geoc_bd09togcj02_multipolygon(geom);
ELSE
             RETURN null;
END CASE;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_bd09togcj02_line"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
p_p     geometry;
        p_t     geometry;
        z_t     geometry;
        i       int;
BEGIN
    i:=1;
    while i <= st_npoints(geom) LOOP
        p_p := st_pointn(geom,i);
        p_t := geoc_bd09togcj02_point(p_p);
        geom:=st_setpoint(geom,i-1,p_t);
        i:=i+1;
end LOOP;
return geom;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_bd09togcj02_multiline"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
i                 geometry;
        transform_i       geometry;
        multiArr          geometry[];

BEGIN
    multiArr:='{}'::geometry[];
for i in EXECUTE $Q$ select (st_dump($1)).geom $Q$ using geom LOOP
          transform_i :=geoc_bd09togcj02_line(i);
multiArr := array_append(multiArr, transform_i);
end LOOP;
return st_multi(ST_Union(multiArr));
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_bd09togcj02_multipoint"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
i                 geometry;
        transform_i       geometry;
        multiArr          geometry[];

BEGIN
    multiArr:='{}'::geometry[];
for i in EXECUTE $Q$ select (st_dump($1)).geom $Q$ using geom LOOP
          transform_i :=geoc_bd09togcj02_point(i);
multiArr := array_append(multiArr, transform_i);
end LOOP;
return st_multi(ST_Union(multiArr));
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_bd09togcj02_multipolygon"("source_geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
target_parts    geometry[];
    single_polygon  geometry;
    single_polygon_trans  geometry;
    final_geom      geometry;

BEGIN
    IF ST_GeometryType(source_geom) != 'ST_MultiPolygon' THEN
        RETURN null;
END IF;
FOR single_polygon IN SELECT (ST_Dump($1)).geom LOOP
        single_polygon_trans := geoc_bd09togcj02_polygon(single_polygon);
target_parts := array_append(target_parts,single_polygon_trans);
END LOOP;

SELECT st_multi(ST_Union(target_parts)) INTO final_geom;

RETURN final_geom;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_bd09togcj02_point"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
x       numeric;
    y       numeric;
     z       double precision;
     theta   double precision;
     x_pi    double precision:=3.14159265358979324 * 3000.0 / 180.0;
    gcj_point  geometry;

BEGIN
    if st_geometrytype(geom) != 'ST_Point' then
        return null;
end if;
    x := st_x(geom);
    y := st_y(geom);
    if (geoc_is_in_china_bbox(x, y) = false) then
        return geom;
end if;
    x:= ST_X(geom) - 0.0065;
    y:= ST_Y(geom) - 0.006;
    z:=sqrt(power(x,2) + power(y,2)) - 0.00002 *sin(y * x_pi);
    theta:= atan2(y, x) - 0.000003 * cos(x * x_pi);
      gcj_point:=ST_SetSRID(ST_MakePoint(z *cos(theta),z *sin(theta)),4326);
return gcj_point;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_bd09togcj02_polygon"("source_geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
target_parts    geometry[];
    source_npoints  integer;
    single_line     geometry;
    single_line_trans geometry;
    single_polygon  geometry;
    final_geom      geometry;

BEGIN
    IF ST_GeometryType(source_geom) != 'ST_Polygon' THEN
        RETURN null;
END IF;

FOR single_polygon IN SELECT ST_ExteriorRing ((st_dumprings($1)).geom) as geom LOOP
        source_npoints := ST_NPoints(single_polygon);
single_line  := ST_RemovePoint(single_polygon, source_npoints - 1);
        single_line_trans := geoc_bd09togcj02_line(single_line);
        target_parts := array_append(target_parts, ST_AddPoint(single_line_trans, ST_PointN(single_line_trans, 1)));
END LOOP;
SELECT ST_MakePolygon(target_parts[1], target_parts[2:array_upper(target_parts, 1)]) INTO final_geom;

RETURN final_geom;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_bd09towgs84"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
i                 geometry;
        transform_i       geometry;
        multiArr          geometry[];

BEGIN
    IF st_srid(geom) != '4490' and  st_srid(geom) != '4326'THEN
        RETURN null;
end if;
CASE ST_GeometryType(geom)
        when 'ST_LineString' then
            return geoc_bd09towgs84_line(geom);
when 'ST_MultiLineString' then
            return geoc_bd09towgs84_multiline(geom);
when 'ST_Point' then
            return geoc_bd09towgs84_point(geom);
when 'ST_MultiPoint' then
            return geoc_bd09towgs84_multipoint(geom);
when 'ST_Polygon' then
            return geoc_bd09towgs84_polygon(geom);
when 'ST_MultiPolygon' then
            return geoc_bd09towgs84_multipolygon(geom);
ELSE
             RETURN null;
END CASE;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_bd09towgs84_line"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
p_p     geometry;
        p_t     geometry;
        z_t     geometry;
        i       int;
BEGIN
    i:=1;
    while i <= st_npoints(geom) LOOP
        p_p := st_pointn(geom,i);
        p_t := geoc_bd09towgs84_point(p_p);
        geom:=st_setpoint(geom,i-1,p_t);
        i:=i+1;
end LOOP;
return geom;

END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_bd09towgs84_multiline"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
i                 geometry;
        transform_i       geometry;
        multiArr          geometry[];

BEGIN
    multiArr:='{}'::geometry[];
for i in EXECUTE $Q$ select (st_dump($1)).geom $Q$ using geom LOOP
          transform_i :=geoc_bd09towgs84_line(i);
multiArr := array_append(multiArr, transform_i);
end LOOP;
return st_multi(ST_Union(multiArr));
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;
CREATE OR REPLACE FUNCTION "public"."geoc_bd09towgs84_multipoint"("geom" "public"."geometry")
  RETURNS "public"."geometry" AS $BODY$
DECLARE
i                 geometry;
        transform_i       geometry;
        multiArr          geometry[];

BEGIN
    multiArr:='{}'::geometry[];
for i in EXECUTE $Q$ select (st_dump($1)).geom $Q$ using geom LOOP
          transform_i :=geoc_bd09towgs84_point(i);
multiArr := array_append(multiArr, transform_i);
end LOOP;
return st_multi(ST_Union(multiArr));
END;
$BODY$
LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE OR REPLACE FUNCTION "pipeline"."getBinFloat"("colValue" float8, "rangeList" text)
  RETURNS "pg_catalog"."float8" AS $BODY$
    import json
    def begin(colValue, rangeList):
        try:
            _list = json.loads(rangeList)
            for item in _list:
                if colValue >= item[0] and colValue < item[1]:
                    return item[0]
        except Exception as e:
            return -1.0
        return -1.0
    if __name__ == "__main__":
        return begin(colValue, rangeList)
$BODY$
  LANGUAGE plpython3u VOLATILE
  COST 100;

-- auto-generated definition
create function sys_func_format_time(data character varying, pattern character varying) returns text
    language plpython3u
as
$$
import time
import re
from dateutil.parser import parse

TIMESTAMP_10 = r"^\d{10}(\.\d+)?$"
TIMESTAMP_13 = r"^\d{13}$"

def format_time(data, pattern):
    if data == None:
        return None

    data = data.replace("年", "-").replace("月", "-").replace(",", ", ")
    data = re.compile(
        r"日|Mon\.|Tue\.|Wed\.|Thur\.|Fri\.|Sat\.|Sun\.|Monday|Tuesday|Wednesday|Thursday|Friday|Saturday|Sunday").sub(
        "", data)
    if pattern == "timestamp":
        if re.match(TIMESTAMP_10, data):
            ret = data
        elif re.match(TIMESTAMP_13, data):
            ret = int(int(data) / 1000)
        else:
            pattern = "%Y-%m-%d %H:%M:%S"
            ret = str(int(time.mktime(time.strptime(parse(data).strftime(pattern), pattern))))
            if len(ret) == 9:
                ret = "0"+ret
        return ret
    else:
        if re.match(TIMESTAMP_10, data):
            ret = time.strftime(pattern, time.localtime(int(data)))
        elif re.match(TIMESTAMP_13, data):
            ret = time.strftime(pattern, time.localtime(float(data)/1000))
        else:
            ret = parse(data).strftime(pattern)

        if pattern.endswith("%p"):
            splits = ret.split(" ")
            wrong = splits[-2]
            wrong_num = int(wrong[:2])
            if wrong_num >= 12:
                correct = str(wrong_num-12)
                if len(correct) == 1:
                    correct = "0"+correct
                splits[-2] = correct+wrong[2:]
                ret = " ".join(splits)
        return ret

if __name__ == "__main__":
    ret = format_time(data, pattern)
    return ret
$$;

CREATE OR REPLACE FUNCTION "pipeline"."sys_func_pivot_table_output_col"("table_name" varchar, "rowc" varchar, "cols" _varchar, "colsize" int4, "statistic" varchar)
  RETURNS "pg_catalog"."varchar" AS $BODY$
declare
    sql_1 varchar;
    sql_2 varchar;
    sql_3 varchar;
    sql_4 varchar;
        groupsql_1 varchar;
        col varchar;
        colc varchar;
    meth varchar;
    meth_name varchar;
    stcs RECORD;
        num integer;
        col_length integer;
        row_length integer;
        sql_part_1 varchar;
        cols_str varchar;
        col_limit integer;
        col_limit_p integer;

begin
    cols_str = array_to_string(cols,',');
        groupsql_1 = ' from '||table_name||' group by '||cols_str||' order by '||cols_str;
        sql_3 = '';
        row_length = array_length(string_to_array(rowc,','), 1)+1;
        col_length = array_length(cols, 1);

        if colsize<2 then
            return '限制长度不能小于2';
        end if;
        if colsize>1600 then
            return '最大限制长度为1600';
        end if;
        if row_length>colsize then
            return '限制长度至少比行标签个数多一个';
        end if;

        col_limit = colsize-row_length+1;

        for stcs in (select json_array_elements(statistic::json)->>'col' col,json_array_elements(statistic::json)->>'method' meth,json_array_elements(statistic::json)->>'name' name ) loop
            col = stcs.col;
            meth = stcs.meth;
            meth_name = stcs.name;
            continue when col='' or meth='';
            if meth_name = '' or meth_name is null then
                meth_name = meth;
            end if;

            if col_length=1 then
                colc = cols[1];
                sql_1 = colc||'||''_'||col||'_'||meth_name||'''';
            else
                for num in 1..col_length loop
                    colc = cols[num];
                    if num=1 then
                        sql_part_1 = colc||'||''_''';
                    else
                        sql_part_1 = sql_part_1||'||'||colc||'||''_''';
                    end if;
                end loop;
                sql_1 = sql_part_1||'||'''||col||'_'||meth_name||'''';
            end if;
            sql_2 = 'select string_agg(new_col,'','')from(select '||sql_1||' new_col '||groupsql_1||' limit '||col_limit||') a';
            execute sql_2 into sql_4;
            if  sql_3 = '' then
                sql_3 = sql_4;
            else
                sql_3 = sql_3||','||sql_4;
            end if;
            col_limit_p = array_length(string_to_array(sql_4,','), 1);
            col_limit = col_limit-col_limit_p;
            exit when col_limit =0;
        end loop;

        return rowc||','||sql_3||',_record_id_';

end
$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100
;

CREATE OR REPLACE FUNCTION "pipeline"."sys_func_pivot_table_view_create"("table_name" varchar, "view_name" varchar, "rowc" varchar, "cols" _varchar, "colsize" int4, "statistic" varchar)
  RETURNS "pg_catalog"."varchar" AS $BODY$
declare
    sql_1 varchar;
    sql_2 varchar;
    sql_3 varchar;
    sql_4 varchar;
    sql_5 varchar;
        groupsql_1 varchar;
        groupsql_2 varchar;
        exe_sql varchar;
        col varchar;
        colc varchar;
    meth varchar;
    meth_name varchar;
    stcs RECORD;
        num integer;
        col_length integer;
        row_length integer;
        sql_part_1 varchar;
        cols_str varchar;
        col_limit integer;
        col_limit_p integer;
    sql_count varchar;

begin
    cols_str = array_to_string(cols,',');
        groupsql_1 = ' from '||table_name||' group by '||rowc||' order by '||rowc;
        groupsql_2 = ' from '||table_name||' group by '||cols_str||' order by '||cols_str;
        sql_3 = '';
        row_length = array_length(string_to_array(rowc,','), 1)+1;
        col_length = array_length(cols, 1);

        if colsize<2 then
            return '限制长度不能小于2';
        end if;
        if colsize>1600 then
            return '最大限制长度为1600';
        end if;
        if row_length>colsize then
            return '限制长度至少比行标签个数多一个';
        end if;

        col_limit = colsize-row_length+1;

        for stcs in (select json_array_elements(statistic::json)->>'col' col,json_array_elements(statistic::json)->>'method' meth,json_array_elements(statistic::json)->>'name' name ) loop
            col = stcs.col;
            meth = stcs.meth;
            meth_name = stcs.name;
            continue when col='' or meth='';
            if meth_name = '' or meth_name is null then
                meth_name = meth;
            end if;

            if col_length=1 then
                colc = cols[1];
                sql_1 = ''''||meth||'(case when "'||colc||'"=''''''||'||colc||'||'''||''''' then "'||col||'" end) as "''||'||colc||'||''_'||col||'_'||meth_name||'"''';
            else
                for num in 1..col_length loop
                    colc = cols[num];
                    if num=1 then
                        sql_1 = ''''||meth||'(case when "'||colc||'"=''''''||'||colc||'||'''||''''' ';
                        sql_part_1 = colc||'||''_''';
                    else
                        sql_1 = sql_1||' and "'||colc||'"=''''''||'||colc||'||'''||''''' ';
                        sql_part_1 = sql_part_1||'||'||colc||'||''_''';
                    end if;
                end loop;
                sql_1 = sql_1||' then "'||col||'" end) as "''||'||sql_part_1||'||'''||col||'_'||meth_name||'"''';
            end if;
            sql_2 = 'select string_agg(new_col,'','')from(select '||sql_1||' new_col '||groupsql_2||' limit '||col_limit||') a';
            execute sql_2 into sql_5;
            if  sql_3 = '' then
                sql_3 = sql_5;
            else
                sql_3 = sql_3||','||sql_5;
            end if;
            col_limit_p = array_length(string_to_array(sql_5,','), 1);
            col_limit = col_limit-col_limit_p;
            exit when col_limit =0;
        end loop;

    sql_4 = 'select '||rowc||','||sql_3||',row_number() over() as _record_id_ '||groupsql_1||';';
      exe_sql = 'create view '||view_name||' as '||sql_4;
       execute exe_sql;
        return '成功';

end
$BODY$
  LANGUAGE 'plpgsql' VOLATILE COST 100
;

CREATE OR REPLACE FUNCTION "public"."hmac"(text, text, text)
  RETURNS "pg_catalog"."bytea" AS '$libdir/pgcrypto', 'pg_hmac'
  LANGUAGE c IMMUTABLE STRICT
  COST 1
;

CREATE OR REPLACE FUNCTION "public"."hmac"(bytea, bytea, text)
  RETURNS "pg_catalog"."bytea" AS '$libdir/pgcrypto', 'pg_hmac'
  LANGUAGE c IMMUTABLE STRICT
  COST 1
;
